package db.tables;


import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import control.ConnectionManage;


import db.parking.beans.Customer;
import db.parking.beans.Orders;
import db.parking.beans.Subscriber;


public class AdminSubscriber {
	
	private static Connection conn = ConnectionManage.getInstance().getConnection();

	public static Subscriber getRow(int sid) throws SQLException {

		String sql = "SELECT * FROM Subscriber  WHERE subID = ?";
		ResultSet rs = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setInt(1, sid);


			rs = stmt.executeQuery();

			if (rs.next()) {
				Subscriber bean = new Subscriber();
				bean.setSubID(rs.getInt("subID"));
				bean.setCusID(rs.getString("cusID"));
				bean.setCarNo(rs.getString("carNo"));
				bean.setSubStart(rs.getTimestamp("subStart"));
				bean.setType(rs.getString("type"));
				bean.setLotNo(rs.getInt("lotNo"));
				bean.setLeaveTime(rs.getTimestamp("leaveTime"));
				bean.setStatus(rs.getInt("status"));

				return bean;
			} else {
				return null;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}

	public static Subscriber getRow(int subID,String carNo) throws SQLException {

		String sql = "SELECT * FROM Subscriber  WHERE subID = ? AND carNo = ?";
		ResultSet rs = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setInt(1, subID);
			stmt.setString(2, carNo);

			rs = stmt.executeQuery();

			if (rs.next()) {
				Subscriber bean = new Subscriber();
				bean.setSubID(rs.getInt("subID"));
				bean.setCusID(rs.getString("cusID"));
				bean.setCarNo(rs.getString("carNo"));
				bean.setSubStart(rs.getTimestamp("subStart"));
				bean.setType(rs.getString("type"));
				bean.setLotNo(rs.getInt("lotNo"));
				bean.setLeaveTime(rs.getTimestamp("leaveTime"));
				bean.setStatus(rs.getInt("status"));

			
				return bean;
			} else {
				return null;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	
	public static Subscriber getRow(String cusID,String carNo) throws SQLException {

		String sql = "SELECT * FROM Subscriber  WHERE cusID = ? AND carNo = ?";
		ResultSet rs = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setString(1, cusID);
			stmt.setString(2, carNo);

			rs = stmt.executeQuery();

			if (rs.next()) {
				Subscriber bean = new Subscriber();
				bean.setSubID(rs.getInt("subID"));
				bean.setCusID(rs.getString("cusID"));
				bean.setCarNo(rs.getString("carNo"));
				bean.setSubStart(rs.getTimestamp("subStart"));
				bean.setType(rs.getString("type"));
				bean.setLotNo(rs.getInt("lotNo"));
				bean.setLeaveTime(rs.getTimestamp("leaveTime"));
				bean.setStatus(rs.getInt("status"));

			
				return bean;
			} else {
				return null;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	public static boolean insertFull(Subscriber bean) throws Exception {
    //complaintID is autoIncremented on Insert
		String sql = "INSERT into Subscriber (cusID , carNo , subStart , type , status) " +
				"VALUES (?, ?, ?, ?, ?)";
		ResultSet keys = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				) {
			
			stmt.setString(1, bean.getCusID());
			stmt.setString(2, bean.getCarNo());
			stmt.setTimestamp(3,bean.getSubStart());
			stmt.setString(4, bean.getType());
			stmt.setInt(5, bean.getStatus());
			
			
			int affected = stmt.executeUpdate();
			if(affected ==1){
				keys = stmt.getGeneratedKeys();
				keys.next();
				int newKey = keys.getInt(1);
	            bean.setSubID(newKey);
			}else {
				System.err.println("No Rows affected");
				return false;
			}
	            
		} catch (SQLException e) {
			System.err.println(e);
			return false;
		} finally{
				 if (keys != null) 
			            keys.close();
		}
		return true;
		
	}
	
	public static boolean insertRegular(Subscriber bean) throws Exception {
	    //complaintID is autoIncremented on Insert
			String sql = "INSERT into Subscriber (cusID , carNo , subStart , type , lotNo , leaveTime , status) " +
					"VALUES (?, ?, ?, ?, ? ,? , ?)";
			ResultSet keys = null;
			try (
					PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					) {
				
				stmt.setString(1, bean.getCusID());
				stmt.setString(2, bean.getCarNo());
				stmt.setTimestamp(3,bean.getSubStart());
				stmt.setString(4, bean.getType());
				stmt.setInt(5, bean.getLotNo());
				stmt.setTimestamp(6,bean.getLeaveTime());
				stmt.setInt(7, bean.getStatus());


				int affected = stmt.executeUpdate();
				if(affected ==1){
					keys = stmt.getGeneratedKeys();
					keys.next();
					int newKey = keys.getInt(1);
		            bean.setSubID(newKey);
				}else {
					System.err.println("No Rows affected");
					return false;
				}
			
			} catch (SQLException e) {
				System.err.println(e);
				return false;
			} finally{
				if(keys != null)
					keys.close();
			}
			return true;
			
		}
	
	public static boolean update(Subscriber bean) throws Exception {

		String sql =
				"UPDATE Subscriber SET " +
				"status = ? " +
				"WHERE subID = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setInt(1,bean.getStatus());
			stmt.setInt(2,bean.getSubID());
			
			int affected = stmt.executeUpdate();
			if(affected == 1){
				return true;
			}else{
				return false;
			}
			
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}

	}
	
}
